using System;
using System.Data;
using System.Diagnostics;
using System.Collections.Generic;
using System.IO;

/// <summary>
/// The abstract Query class provides a base class to implement database query.
/// </summary>
/// <remarks>
/// <author>Samuel Chen</author>
/// </remarks>
namespace Net.SamuelChen.Libs.Data.Database {
    public abstract class Query : IQuery {
        #region Variants
        
        private string _connString;
        private IDbConnection _conn;

        #endregion

        #region Construction & Destruction

        public Query() {
            _conn = CreateConnection();
        }

        public Query(string ConnectionString) {
            _connString = ConnectionString;
            _conn = CreateConnection();
        }

        public void Dispose() {
            if (_conn != null)
                if (_conn.State != ConnectionState.Closed)
                    _conn.Close();

            _conn = null;
            GC.SuppressFinalize(this);
            Debug.WriteLine("Database connection closed.");
        }
        #endregion

        #region Properties
        public string ConnectionString {
            get { return _connString; }
            set {
                if (_conn.State != ConnectionState.Closed)
                    _conn.Close();
                _connString = value;
                _conn.ConnectionString = _connString;
                Debug.WriteLine("Set connection string to: " + _connString);
            }
        }

        public IDbConnection Connection {
            get { return _conn; }
        }
        #endregion

        #region protected methods
        abstract protected IDbConnection CreateConnection();
        abstract protected IDataAdapter CreateAdapter(IDbCommand selectCommand);

        public string LoadQuery(string path)  {
            string sql;

            try {
                StreamReader ss = new StreamReader(path);
                sql = ss.ReadToEnd();
                ss.Close();
                ss = null;
                Debug.WriteLine("Loaded sql statement from " + path + ".");
                Debug.WriteLine("Sql statement: " + sql);
            } catch (Exception ex) {
                Debug.WriteLine("Can not access file " + path + ".\n" + ex.ToString());
                throw new DBException("Can not access file " + path + ".\n" + ex.ToString());
            }

            return sql;
        }

        protected bool FillDataset(IDataAdapter adapter, out DataSet ds) {
            ds = new DataSet();

            if (adapter == null) return false;

            bool ret = false;

            try {
                adapter.Fill(ds);
                ret = true;
            } catch (Exception ex) {
                throw new DBException("Bad adapter.", ex);
            }

            return ret;
        }
        #endregion

        #region IQuery Members

        #region ExecSql
        public bool ExecSql(string sql) {
            IDbCommand cmd = this.CreateCommand();
            cmd.CommandText = sql;
            return this.ExecCommand(cmd);
        }

        public bool ExecSql(string sql, out IDataReader reader) {
            IDbCommand cmd = this.CreateCommand();
            cmd.CommandText = sql;
            return this.ExecCommand(cmd, out reader);
        }

        public bool ExecSql(string sql, DataSet ds) {
            IDbCommand cmd = this.CreateCommand();
            cmd.CommandText = sql;
            return this.ExecCommand(cmd, ds);
        }
        #endregion

        #region ExecStoredProcedure
        public bool ExecStoredProcedure(string sp, IList<IDataParameter> parms) {
            IDbCommand cmd = this.CreateCommand();
            cmd.CommandText = sp;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Clear();
            int count = (parms == null ? 0 : parms.Count);
            for (int i = 0; i < count; i++) {
                cmd.Parameters.Add(parms[i]);
            }
            return this.ExecCommand(cmd);
        }

        public bool ExecStoredProcedure(string sp, IList<IDataParameter> parms, IDataReader reader) {
            IDbCommand cmd = this.CreateCommand();
            cmd.CommandText = sp;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Clear();
            int count = (parms == null ? 0 : parms.Count);
            for (int i = 0; i < count; i++) {
                cmd.Parameters.Add(parms[i]);
            }
            return this.ExecCommand(cmd, out reader);
        }

        public bool ExecStoredProcedure(string sp, IList<IDataParameter> parms, System.Data.DataSet ds) {
            IDbCommand cmd = this.CreateCommand();
            cmd.CommandText = sp;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Clear();
            int count = (parms == null ? 0 : parms.Count);
            for (int i = 0; i < count; i++) {
                cmd.Parameters.Add(parms[i]);
            }
            return this.ExecCommand(cmd, ds);
        }

        #endregion

        #region ExecCommand
        public bool ExecCommand(System.Data.IDbCommand command) {
            if (command == null) return false;

            bool ret = false;

            try {
                Debug.WriteLine("Query: " + command.CommandText);
                command.Connection = this.Connection;
                this.Connection.Open();
                int num = command.ExecuteNonQuery();
                this.Connection.Close();
                Debug.WriteLine(num.ToString() + " line(s) data effort.");
                ret = true;
            } catch (Exception ex) {
                throw new DBException("Bad command.", ex);
            }

            return ret;
        }

        public bool ExecCommand(IDbCommand command, out System.Data.IDataReader reader) {
            bool ret = false;

            try {
                Debug.WriteLine("Query: " + command.CommandText);
                command.Connection = this.Connection;
                this.Connection.Open();
                reader = command.ExecuteReader();
                // Connection must be closed outside after reader used.
                ret = true;
            } catch (Exception ex) {
                throw new DBException("Bad command.", ex);
            }

            return ret;
        }

        public bool ExecCommand(IDbCommand command, System.Data.DataSet ds) {
            try {
                Debug.WriteLine("Excuting stored procedure: " + command.CommandText);
                command.Connection = this.Connection;
                return this.FillDataset(this.CreateAdapter(command), out ds);
            } catch (Exception ex) {
                throw new DBException("bad command", ex);
            }
        }

        #endregion

        #region ExecScalar

        public object ExecScalar(IDbCommand command) {
            object obj = null;

            try {
                Debug.WriteLine("Query: " + command.CommandText);
                command.Connection = this.Connection;
                obj = command.ExecuteScalar();
            } catch (Exception ex) {
                throw new DBException("Bad command.", ex);
            }

            return obj;
        }

        public object ExecScalar(string sp, IList<IDataParameter> parms) {
            IDbCommand cmd = this.CreateCommand();
            cmd.CommandText = sp;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Clear();
            int count = (parms == null ? 0 : parms.Count);
            for (int i = 0; i < count; i++) {
                cmd.Parameters.Add(parms[i]);
            }
            bool obj = this.ExecCommand(cmd);
            return obj;
        }

        public object ExecScalar(string sql) {
            IDbCommand cmd = this.CreateCommand();
            cmd.CommandText = sql;
            return this.ExecCommand(cmd);
        }

        #endregion

        abstract public IDbCommand CreateCommand();
        abstract public IDataParameter CreateParameter(string parameterName, object value);
        abstract public IDataParameter CreateParameter(string parameterName, object value, ParameterDirection direction);
        
        #endregion
    }
}
